IF EXISTS (SELECT * FROM sysobjects WHERE name = 'GetDatabaseFileMSSQLIdForServerDatabase' AND type IN ('FN','IF','TF'))
     DROP FUNCTION dbo.GetDatabaseFileMSSQLIdForServerDatabase
GO

CREATE FUNCTION dbo.GetDatabaseFileMSSQLIdForServerDatabase
     ( @SrvrInstncDatabaseId     int,
       @DatabaseFileId           int,
       @DatabaseFileGUID         uniqueidentifier,
       @FileIsLogFileFlag        tinyint,
       @LogicalFileName          varchar(128),
       @PhysicalFileNameWithPath varchar(255)
     )
RETURNS int
AS
/*
 *
 * GetDatabaseFileMSSQLIdForServerDatabase
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Note(s):      When other server types are supported this function must be
 *               updated to handle them.
 *
 * CfgCtrl Keys: 
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 10/27/2011, 1.00.0000
 *
 */
BEGIN
     DECLARE @ReturnValue      int,
             @SrvrInstncTypeId int 

     -- Determine Segment Id
     SELECT @SrvrInstncTypeId = SrvrInstncTypeId 
     FROM   dbo.SrvrInstnc si INNER JOIN dbo.SrvrInstncDatabase sid
               ON si.SrvrInstncId = sid.SrvrInstncId
     WHERE  sid.SrvrInstncDatabaseId = @SrvrInstncDatabaseId

     IF ( @SrvrInstncTypeId = 1 )
     BEGIN -- SQL Server
          IF ( @DatabaseFileGUID IS NOT NULL )
          BEGIN
               -- Start with the easiest method, look at the GUID, this is also
               -- the most reliable method.
               SELECT @ReturnValue = SrvrInstncDatabaseFileMSSQLId
               FROM   dbo.SrvrInstncDatabaseFileMSSQL
               WHERE  SrvrInstncDatabaseId = @SrvrInstncDatabaseId
               AND    DatabaseFileGUID     = @DatabaseFileGUID

               IF ( @ReturnValue IS NOT NULL ) GOTO FINISHED
          END

          -- Ok, try with the database id, logical filename and physical filename with path
          SELECT @ReturnValue = SrvrInstncDatabaseFileMSSQLId
          FROM   dbo.SrvrInstncDatabaseFileMSSQL
          WHERE  SrvrInstncDatabaseId     = @SrvrInstncDatabaseId
          AND    DatabaseFileId           = @DatabaseFileId
          AND    LogicalFileName          = @LogicalFileName
          AND    PhysicalFileNameWithPath = @PhysicalFileNameWithPath

          IF ( @ReturnValue IS NOT NULL ) GOTO FINISHED

          -- Ok, try with the logical filename and physical filename with path
          SELECT @ReturnValue = SrvrInstncDatabaseFileMSSQLId
          FROM   dbo.SrvrInstncDatabaseFileMSSQL
          WHERE  SrvrInstncDatabaseId     = @SrvrInstncDatabaseId
          AND    LogicalFileName          = @LogicalFileName
          AND    PhysicalFileNameWithPath = @PhysicalFileNameWithPath

          IF ( @ReturnValue IS NOT NULL ) GOTO FINISHED

          -- We didn't find one so we create one.
          SELECT @ReturnValue = ISNULL( MAX( SrvrInstncDatabaseFileMSSQLId ), 0 ) + 1
          FROM   dbo.SrvrInstncDatabaseFileMSSQL
          WHERE  SrvrInstncDatabaseId = @SrvrInstncDatabaseId

          GOTO FINISHED
     END

     IF ( @SrvrInstncTypeId = 2 )
     BEGIN -- Oracle
          SELECT @ReturnValue = NULL
          GOTO FINISHED
     END

     IF ( @SrvrInstncTypeId = 3 )
     BEGIN -- Sybase
          SELECT @ReturnValue = NULL
          GOTO FINISHED
     END

     FINISHED:
     RETURN @ReturnValue
END
GO
